<?php

/**
 * ***** 迈若数据库操作类 for pdo ******
 * ============================================================================
 * 版权所有 2008-2010 上海迈若网络科技有限公司、OKYO Studio，并保留所有权利。
 * 网站地址: http://www.manro.com.cn、 http://www.okyo.cn
 * 说明：迈若数据库操作类分MySQL，MySQLi，PDO 3个部分
 *      以统一的数据库操作接口应对不同的服务器环境下的数据库操作问题
 * ============================================================================
 * @author Dicky Pan
 * @copyright Dicky Pan, manro.com.cn, okyo.cn
 * @version 1.1.4
 *
 * 1.1.1 为SQL语句中字段加入``
 * 1.1.2 获取数据表里的所有字段
 * 1.1.3 getOneRecord方法在没有获取到记录时返回False
 * 1.1.4 加入字段类型获取方法getFieldsType，在insert，update方法中建立SQL语句时根据字段类型判断空值语句
 */
class DBO {

    public static $host;  //Default : localhost
    public static $user;
    public static $password;
    public static $database;
    public static $charset;      //Default : utf8
    public static $port;  //Default : 3306
    public static $always_open;  //开启或关闭长连接   Defalut：True
    private $conn; //数据库联接对象
    private $result; //查询结果
    public $resultNum; //查询结果计数
    public $debug;

    /**
     * 构造函数
     */
    function __construct() {
        //设置数据库默认属性
        if (empty(self::$charset)) {
            self::$charset = 'utf8';
        }
        if (empty(self::$host)) {
            self::$host = 'localhost';
        }
        if (empty(self::$port)) {
            self::$port = 3306;
        }
        if (empty(self::$always_open)) {
            self::$always_open = true;
        }

        $this->debug = true;

        //打开数据库
        $this->conn = $this->open();

        //数据库连接出错程序终止
        if (empty($this->conn)) {
            exit();
        }
    }

    /**
     * 析构函数
     */
    function __destruct() {
        if (isset($this->conn)) {
            $this->conn = null;
        }
    }

    /**
     * 直接输出错误
     *
     * @param  string      $message 错误描述
     * @param  boolean     $is_print 是否直接输出错误
     * @return mixed       不是直接输出错误时返回错误描述，否则直接输出错误描述
     */
    private function Error($message, $isPrint = ture) {
        if ($isPrint) {
            die($message);
            return false;
        } else {
            return $message;
        }
    }

    /**
     * 打开数据库
     *
     * @return mixed      返回数据库对象,发生错误则返回false
     */
    private function open() {
        try {
            $dsn = 'mysql:host=' . self::$host . ';dbname=' . self::$database . ';port=' . self::$port . ';charset=' . self::$charset;
            if (self::$always_open) {
                $db = new PDO($dsn, self::$user, self::$password, array(PDO::ATTR_PERSISTENT => true));
            } else {
                $db = new PDO($dsn, self::$user, self::$password);
            }
            $db->exec('set names \'' . self::$charset . '\'');
            return $db;
        } catch (PDOException $e) {
            $this->Error($e->getMessage());
            return null;
        }
    }

    /**
     * 关闭数据库
     *
     * @return null
     */
    private function close() {
        $this->conn = null;
    }

    /**
     * 将查询结果保存为二维数组
     *
     * @return array        执行成功返回一个由二维数组组成的数据集，否则返回false
     */
    private function getResultArray() {
        $this->result->setFetchMode(PDO::FETCH_ASSOC);
        $this->resultNum = $this->result->rowcount();
        return $this->result->fetchAll();
    }

    /**
     * 向数据表插入记录
     *
     * @param   array       $data           以字段名为索引的一维数组
     * @param   string      $table          将要写入数据的表名
     * @return  mixed       执行成功返回新记录ID，执行失败返回flase
     */
    public function insert($data, $table) {
        if (count($data) <= 0) {
            $this->Error('Did not pass any data, the operation is terminated!');
            return false;
        }
        $fideldType = $this->getFieldsType($table);
        while (list($key, $val) = each($data)) {
            $field .= "`$key`,";
            if (preg_match('/int|smallint|tinyint|integer|mediumint|bigint|float|double|decimal|bit/', $fideldType[$key])) {
                if (empty($val)) {
                    $value .= "0,";
                } else {
                    $value .= "$val,";
                }
            } elseif (preg_match('/date/', $fideldType[$key])) {
                if (empty($val)) {
                    $value .= "'0000-00-00',";
                } else {
                     $value .= "'$val',";
                }
            } elseif (preg_match('/time/', $fideldType[$key])) {
                if (empty($val)) {
                    $value .= "'00:00:00',";
                } else {
                    $value .= "'$val',";
                }
            } elseif (preg_match('/datetime/', $fideldType[$key])) {
                if (empty($val)) {
                    $value .= "'0000-00-00 00:00:00',";
                } else {
                     $value .= "'$val',";
                }
            } else {
                $value .= "'$val',";
            }
        }
        $field = substr($field, 0, -1);
        $value = substr($value, 0, -1);
        $sql = "insert into $table($field) values($value)";
        //echo $sql;
        //exit();
        $result = $this->conn->exec($sql);
        if ($this->conn->errorCode() != '00000') {
            $this->query_error($this->conn->errorInfo(), $sql);
            $return_value = false;
        } else {
            $return_value = $this->conn->lastInsertId();
        }
        return $return_value;
    }

    /**
     * 字段值累加
     *
     * @param   array       $field          执行累加的字段
     * @param   string      $table          将要写入数据的表名
     * @param   string      $filter         筛选条件
     * @return  mixed       执行成功返回影响记录数，执行失败返回flase
     */
    public function addUp($field, $table, $filter, $value = 1) {
        $sql = "update `$table` set `$field` = `$field` + $value where $filter";
        $result = $this->conn->exec($sql);
        if ($this->conn->errorCode() != '00000') {
            $this->query_error($this->conn->errorInfo(), $sql);
            $return_value = false;
        } else {
            $return_value = $result;
        }
        return $return_value;
    }

    /**
     * 更新数据记录
     *
     * @param   array       $data           以字段名为索引的一维数组
     * @param   string      $table          将要写入数据的表名
     * @param   string      $filter          筛选条件
     * @return  mixed       执行成功返回影响记录数，执行失败返回flase
     */
    public function update($data, $table, $filter) {
        if (count($data) <= 0) {
            $this->Error('Did not pass any data, the operation is terminated!');
            return false;
        }
        $fideldType = $this->getFieldsType($table);
        while (list($key, $val) = each($data)) {
            if (preg_match('/int|smallint|tinyint|integer|mediumint|bigint|float|double|decimal|bit/', $fideldType[$key])) {
                if (empty($val)) {
                    $updateFideld .= "`$key` = 0,";
                } else {
                    $updateFideld .= "`$key` = $val,";
                }
            } elseif (preg_match('/date/', $fideldType[$key])) {
                if (empty($val)) {
                    $updateFideld .= "`$key` = '0000-00-00',";
                } else {
                    $updateFideld .= "`$key` = '$val',";
                }
            } elseif (preg_match('/time/', $fideldType[$key])) {
                if (empty($val)) {
                    $updateFideld .= "`$key` = '00:00:00',";
                } else {
                    $updateFideld .= "`$key` = '$val',";
                }
            } elseif (preg_match('/datetime/', $fideldType[$key])) {
                if (empty($val)) {
                    $updateFideld .= "`$key` = '0000-00-00 00:00:00',";
                } else {
                    $updateFideld .= "`$key` = '$val',";
                }
            } else {
                $updateFideld .= "`$key` = '$val',";
            }
        }
        $updateFideld = substr($updateFideld, 0, -1);
        $sql = "update $table set $updateFideld where $filter";
        //echo $sql;
        //exit();
        $result = $this->conn->exec($sql);
        if ($this->conn->errorCode() != '00000') {
            $this->query_error($this->conn->errorInfo(), $sql);
            $return_value = false;
        } else {
            $return_value = $result;

        }
        return $return_value;
    }

    /**
     * 删除数据记录
     *
     * @param   string      $table          将要写入数据的表名
     * @param   string      $filter          删除条件
     * @return  mixed       执行成功返回影响记录数，执行失败返回flase
     */
    public function delete($table, $filter) {
        $sql = "delete from $table where $filter";
        $result = $this->conn->exec($sql);
        if ($this->conn->errorCode() != '00000') {
            $this->query_error($this->conn->errorInfo(), $sql);
            $return_value = false;
        } else {
            $return_value = $result;
        }
        return $return_value;
    }

    /**
     * 查询单条记录
     *
     * @param   string      $filter     查询条件
     * @param   string      $table      查询表
     * @param   string      $order      排序
     * @param   string      $select     抓取字段 default *
     * @return  mixed       执行成功返回记录数组，失败则返回flase
     */
    public function getOneRecord($filter, $table, $select = '*', $order = '') {
        $sql = "select $select from $table where $filter";
        if (!empty($order)) {
            $sql .= " order by $order";
        }
        $sql .= ' limit 0,1';
        //echo '<br>'.$sql;
        $this->result = $this->conn->query($sql);
        $this->result->setFetchMode(PDO::FETCH_ASSOC);
        if ($this->conn->errorCode() != '00000') {
            $this->query_error($this->conn->errorInfo(), $sql);
            $return_value = false;
        } else {
            if ($this->result->rowcount() == 0) {
                $return_value = false;
            } else {
                $return_value = $this->result->fetch();
            }
        }
        return $return_value;
    }

    private function query_error($arrErrorInfo, $errSql) {
        if ($this->debug) {
            $this->Error('<h1>Query Error</h1><br />Code: ' . $arrErrorInfo[0] . '<br />Description: ' . $arrErrorInfo[2] . '<br />SQL: ' . $errSql);
        } else {
            $this->Error('<h1>Query Error</h1>');
        }
    }

    /**
     * 执行数据库查询语句
     *
     * @param   string      $sql 数据库查询语句
     * @return  mixed       执行失败返回false，否则根据实际查询类型返回新插入记录ID或者查询结果或者查询语句影响记录数
     */
    public function query($sql) {
        if (empty($sql)) {
            $this->Error('The lack of query!');
            $return_value = false;
        } else {
            $queryOperatet = strtolower(strtok($sql, ' ')); //获得数据库操作类型
            switch ($queryOperatet) {
                case 'select':
                    $this->result = $this->conn->query($sql);
                    if ($this->conn->errorCode() != '00000') {
                        $this->query_error($this->conn->errorInfo(), $sql);
                        $return_value = false;
                    } else {
                        $return_value = $this->getResultArray();
                    }
                    break;
                case 'insert':
                    $this->result = $this->conn->exec($sql);
                    if ($this->conn->errorCode() != '00000') {
                        $this->query_error($this->conn->errorInfo(), $sql);
                        $return_value = false;
                    } else {
                        $return_value = $this->conn->lastInsertId();
                    }
                    break;
                default:
                    $this->result = $this->conn->exec($sql);
                    if ($this->conn->errorCode() != '00000') {
                        $this->query_error($this->conn->errorInfo(), $sql);
                        $return_value = false;
                    } else {
                        $return_value = $this->result;
                    }
            }
        }
        return $return_value;
    }

    /**
     * 获取数据表里的所有字段
     *
     * @param  string 		$table      查询表
     * @return array        执行成功返回一个由一维数组组成的数据集，否则返回false
     */
    public function getFields($tableName) {
        $result = $this->conn->query("DESCRIBE $tableName");
        if ($this->conn->errorCode() != '00000') {
            $this->query_error($this->conn->errorInfo(), $sql);
            $return_value = false;
        } else {
            $result->setFetchMode(PDO::FETCH_ASSOC);
            $tableFieldInfo = $result->fetchAll();
            if (is_array($tableFieldInfo)) {
                $return_value = array();
                foreach ($tableFieldInfo as $key => $val) {
                    $return_value[$val['Field']] = $val['Default'];
                }
                return $return_value;
            }
        }
    }

    public function getFieldsType($tableName) {
        $result = $this->conn->query("DESCRIBE $tableName");
        if ($this->conn->errorCode() != '00000') {
            $this->query_error($this->conn->errorInfo(), $sql);
            $return_value = false;
        } else {
            $result->setFetchMode(PDO::FETCH_ASSOC);
            $tableFieldInfo = $result->fetchAll();
            if (is_array($tableFieldInfo)) {
                $return_value = array();
                foreach ($tableFieldInfo as $key => $val) {
                    $return_value[$val['Field']] = $val['Type'];
                }
                return $return_value;
            }
        }
    }

    public function SQLDataTypeToPHPDataType($SQLDataType) {
        if (preg_match('/int|smallint|tinyint|integer|mediumint/', $SQLDataType)) {
            return 'int';
        } elseif (preg_match('/bigint/', $SQLDataType)) {
            return 'long';
        } elseif (preg_match('/float|double|decimal/', $SQLDataType)) {
            return 'double';
        } else {
            return 'string';
        }
    }

}

?>
